"""
基金数据访问层 (DAO)
提供基金相关数据的专用访问接口
"""

from typing import List, Optional, Dict, Any
from datetime import datetime, date, timedelta
from sqlalchemy import select, and_, or_, desc, asc, func
from sqlalchemy.ext.asyncio import AsyncSession

from .base import FinancialDataDAO
from ..models.fund import (
    FundBasicInfo,
    FundNetValue,
    FundPortfolio,
    FundPerformance,
    FundManager,
    FundDividend
)


class FundBasicInfoDAO(FinancialDataDAO[FundBasicInfo]):
    """基金基本信息数据访问层"""
    
    model = FundBasicInfo
    
    async def get_by_fund_code(self, session: AsyncSession, fund_code: str) -> Optional[FundBasicInfo]:
        """根据基金代码获取基金基本信息"""
        stmt = select(self.model).where(self.model.fund_code == fund_code)
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_by_fund_type(self, session: AsyncSession, fund_type: str) -> List[FundBasicInfo]:
        """根据基金类型获取基金列表"""
        stmt = select(self.model).where(self.model.fund_type == fund_type)
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_fund_company(self, session: AsyncSession, fund_company: str) -> List[FundBasicInfo]:
        """根据基金公司获取基金列表"""
        stmt = select(self.model).where(self.model.fund_company == fund_company)
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_active_funds(self, session: AsyncSession) -> List[FundBasicInfo]:
        """获取活跃基金列表（状态正常且可申购）"""
        stmt = select(self.model).where(
            and_(
                self.model.status == 'active',
                self.model.purchase_status == 'open'
            )
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def search_funds(
        self,
        session: AsyncSession,
        keyword: str,
        fund_type: Optional[str] = None,
        min_scale: Optional[float] = None
    ) -> List[FundBasicInfo]:
        """搜索基金（支持名称、代码、公司模糊搜索）"""
        conditions = [
            or_(
                self.model.fund_name.ilike(f'%{keyword}%'),
                self.model.fund_code.ilike(f'%{keyword}%'),
                self.model.fund_company.ilike(f'%{keyword}%')
            )
        ]
        
        if fund_type:
            conditions.append(self.model.fund_type == fund_type)
        
        if min_scale:
            conditions.append(self.model.fund_scale >= min_scale)
        
        stmt = select(self.model).where(and_(*conditions))
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_large_scale_funds(
        self,
        session: AsyncSession,
        min_scale: float = 10.0  # 10亿
    ) -> List[FundBasicInfo]:
        """获取大规模基金列表"""
        stmt = (
            select(self.model)
            .where(self.model.fund_scale >= min_scale)
            .order_by(desc(self.model.fund_scale))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_risk_level(self, session: AsyncSession, risk_level: str) -> List[FundBasicInfo]:
        """根据风险等级获取基金列表"""
        stmt = select(self.model).where(self.model.risk_level == risk_level)
        result = await session.execute(stmt)
        return list(result.scalars().all())


class FundNetValueDAO(FinancialDataDAO[FundNetValue]):
    """基金净值数据访问层"""
    
    model = FundNetValue
    
    async def get_by_fund_and_date(
        self,
        session: AsyncSession,
        fund_code: str,
        nav_date: date
    ) -> Optional[FundNetValue]:
        """获取指定基金指定日期的净值数据"""
        stmt = select(self.model).where(
            and_(
                self.model.fund_code == fund_code,
                self.model.nav_date == nav_date
            )
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_nav_history(
        self,
        session: AsyncSession,
        fund_code: str,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None,
        limit: int = 1000
    ) -> List[FundNetValue]:
        """获取基金净值历史数据"""
        conditions = [self.model.fund_code == fund_code]
        
        if start_date:
            conditions.append(self.model.nav_date >= start_date)
        if end_date:
            conditions.append(self.model.nav_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.nav_date))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_recent_nav(
        self,
        session: AsyncSession,
        fund_code: str,
        days: int = 30
    ) -> List[FundNetValue]:
        """获取最近N天的基金净值数据"""
        from_date = datetime.now().date() - timedelta(days=days)
        return await self.get_nav_history(session, fund_code, from_date)
    
    async def get_latest_nav(self, session: AsyncSession, fund_code: str) -> Optional[FundNetValue]:
        """获取基金最新净值"""
        stmt = (
            select(self.model)
            .where(self.model.fund_code == fund_code)
            .order_by(desc(self.model.nav_date))
            .limit(1)
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def calculate_returns(
        self,
        session: AsyncSession,
        fund_code: str,
        start_date: date,
        end_date: date
    ) -> Dict[str, float]:
        """计算基金收益率"""
        start_nav = await self.get_by_fund_and_date(session, fund_code, start_date)
        end_nav = await self.get_by_fund_and_date(session, fund_code, end_date)
        
        if not start_nav or not end_nav:
            return {'return_rate': 0.0, 'cumulative_return': 0.0}
        
        return_rate = (end_nav.unit_nav - start_nav.unit_nav) / start_nav.unit_nav
        cumulative_return = (end_nav.cumulative_nav - start_nav.cumulative_nav) / start_nav.cumulative_nav
        
        return {
            'return_rate': float(return_rate),
            'cumulative_return': float(cumulative_return)
        }
    
    async def get_top_performers(
        self,
        session: AsyncSession,
        nav_date: date,
        limit: int = 50
    ) -> List[FundNetValue]:
        """获取指定日期表现最好的基金"""
        stmt = (
            select(self.model)
            .where(self.model.nav_date == nav_date)
            .order_by(desc(self.model.daily_return))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_volatility_analysis(
        self,
        session: AsyncSession,
        fund_code: str,
        days: int = 252  # 一年交易日
    ) -> Dict[str, float]:
        """计算基金波动率分析"""
        from_date = datetime.now().date() - timedelta(days=days)
        nav_data = await self.get_nav_history(session, fund_code, from_date)
        
        if len(nav_data) < 2:
            return {'volatility': 0.0, 'max_drawdown': 0.0, 'sharpe_ratio': 0.0}
        
        # 计算日收益率
        returns = []
        max_nav = 0
        max_drawdown = 0
        
        for nav in reversed(nav_data):  # 按时间正序
            if nav.daily_return is not None:
                returns.append(float(nav.daily_return))
            
            # 计算最大回撤
            if nav.unit_nav > max_nav:
                max_nav = nav.unit_nav
            else:
                drawdown = (max_nav - nav.unit_nav) / max_nav
                max_drawdown = max(max_drawdown, drawdown)
        
        if not returns:
            return {'volatility': 0.0, 'max_drawdown': float(max_drawdown), 'sharpe_ratio': 0.0}
        
        # 计算波动率（年化）
        import statistics
        volatility = statistics.stdev(returns) * (252 ** 0.5)  # 年化波动率
        
        # 计算夏普比率（简化版，假设无风险利率为3%）
        avg_return = statistics.mean(returns) * 252  # 年化收益率
        risk_free_rate = 0.03
        sharpe_ratio = (avg_return - risk_free_rate) / volatility if volatility > 0 else 0
        
        return {
            'volatility': float(volatility),
            'max_drawdown': float(max_drawdown),
            'sharpe_ratio': float(sharpe_ratio)
        }


class FundPortfolioDAO(FinancialDataDAO[FundPortfolio]):
    """基金投资组合数据访问层"""
    
    model = FundPortfolio
    
    async def get_by_fund_code(
        self,
        session: AsyncSession,
        fund_code: str,
        report_date: Optional[date] = None
    ) -> List[FundPortfolio]:
        """获取基金投资组合数据"""
        conditions = [self.model.fund_code == fund_code]
        
        if report_date:
            conditions.append(self.model.report_date == report_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.holding_ratio))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_latest_portfolio(self, session: AsyncSession, fund_code: str) -> List[FundPortfolio]:
        """获取基金最新投资组合"""
        # 获取最新报告日期
        latest_date_stmt = (
            select(self.model.report_date)
            .where(self.model.fund_code == fund_code)
            .order_by(desc(self.model.report_date))
            .limit(1)
        )
        latest_date_result = await session.execute(latest_date_stmt)
        latest_date = latest_date_result.scalar_one_or_none()
        
        if not latest_date:
            return []
        
        return await self.get_by_fund_code(session, fund_code, latest_date)
    
    async def get_top_holdings(
        self,
        session: AsyncSession,
        fund_code: str,
        top_n: int = 10,
        report_date: Optional[date] = None
    ) -> List[FundPortfolio]:
        """获取基金前N大重仓股"""
        portfolio = await self.get_by_fund_code(session, fund_code, report_date)
        return portfolio[:top_n]
    
    async def get_sector_allocation(
        self,
        session: AsyncSession,
        fund_code: str,
        report_date: Optional[date] = None
    ) -> Dict[str, float]:
        """获取基金行业配置"""
        portfolio = await self.get_by_fund_code(session, fund_code, report_date)
        
        sector_allocation = {}
        for holding in portfolio:
            if holding.sector:
                sector_allocation[holding.sector] = sector_allocation.get(holding.sector, 0) + float(holding.holding_ratio)
        
        return sector_allocation
    
    async def get_stock_overlap(
        self,
        session: AsyncSession,
        fund_code1: str,
        fund_code2: str,
        report_date: Optional[date] = None
    ) -> List[Dict[str, Any]]:
        """分析两个基金的重仓股重叠情况"""
        portfolio1 = await self.get_by_fund_code(session, fund_code1, report_date)
        portfolio2 = await self.get_by_fund_code(session, fund_code2, report_date)
        
        # 创建股票代码到持仓比例的映射
        holdings1 = {p.stock_code: float(p.holding_ratio) for p in portfolio1}
        holdings2 = {p.stock_code: float(p.holding_ratio) for p in portfolio2}
        
        # 找出重叠的股票
        overlap_stocks = set(holdings1.keys()) & set(holdings2.keys())
        
        overlap_data = []
        for stock_code in overlap_stocks:
            overlap_data.append({
                'stock_code': stock_code,
                'fund1_ratio': holdings1[stock_code],
                'fund2_ratio': holdings2[stock_code],
                'total_ratio': holdings1[stock_code] + holdings2[stock_code]
            })
        
        # 按总持仓比例排序
        overlap_data.sort(key=lambda x: x['total_ratio'], reverse=True)
        
        return overlap_data


class FundManagerDAO(FinancialDataDAO[FundManager]):
    """基金经理数据访问层"""
    
    model = FundManager
    
    async def get_by_manager_name(self, session: AsyncSession, manager_name: str) -> List[FundManager]:
        """根据基金经理姓名获取管理的基金列表"""
        stmt = select(self.model).where(self.model.manager_name == manager_name)
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_fund_code(self, session: AsyncSession, fund_code: str) -> List[FundManager]:
        """获取基金的历任基金经理"""
        stmt = (
            select(self.model)
            .where(self.model.fund_code == fund_code)
            .order_by(desc(self.model.start_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_current_managers(self, session: AsyncSession, fund_code: str) -> List[FundManager]:
        """获取基金当前的基金经理"""
        stmt = select(self.model).where(
            and_(
                self.model.fund_code == fund_code,
                self.model.end_date.is_(None)
            )
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_manager_performance(
        self,
        session: AsyncSession,
        manager_name: str
    ) -> Dict[str, Any]:
        """获取基金经理业绩统计"""
        funds = await self.get_by_manager_name(session, manager_name)
        
        total_funds = len(funds)
        total_experience = 0
        
        for fund in funds:
            if fund.end_date:
                experience = (fund.end_date - fund.start_date).days
            else:
                experience = (datetime.now().date() - fund.start_date).days
            total_experience += experience
        
        avg_experience = total_experience / total_funds if total_funds > 0 else 0
        
        return {
            'total_funds': total_funds,
            'avg_experience_days': avg_experience,
            'avg_experience_years': avg_experience / 365.25
        }
    
    async def get_experienced_managers(
        self,
        session: AsyncSession,
        min_years: float = 3.0
    ) -> List[str]:
        """获取经验丰富的基金经理列表"""
        min_days = int(min_years * 365.25)
        current_date = datetime.now().date()
        
        stmt = select(self.model.manager_name).where(
            or_(
                and_(
                    self.model.end_date.is_not(None),
                    func.extract('days', self.model.end_date - self.model.start_date) >= min_days
                ),
                and_(
                    self.model.end_date.is_(None),
                    func.extract('days', current_date - self.model.start_date) >= min_days
                )
            )
        ).distinct()
        
        result = await session.execute(stmt)
        return [name for name, in result.fetchall()]


class FundDividendDAO(FinancialDataDAO[FundDividend]):
    """基金分红数据访问层"""
    
    model = FundDividend
    
    async def get_by_fund_code(self, session: AsyncSession, fund_code: str) -> List[FundDividend]:
        """获取基金分红历史"""
        stmt = (
            select(self.model)
            .where(self.model.fund_code == fund_code)
            .order_by(desc(self.model.dividend_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_recent_dividends(
        self,
        session: AsyncSession,
        days: int = 90
    ) -> List[FundDividend]:
        """获取最近分红的基金"""
        from_date = datetime.now().date() - timedelta(days=days)
        stmt = (
            select(self.model)
            .where(self.model.dividend_date >= from_date)
            .order_by(desc(self.model.dividend_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_dividend_statistics(
        self,
        session: AsyncSession,
        fund_code: str
    ) -> Dict[str, Any]:
        """获取基金分红统计"""
        dividends = await self.get_by_fund_code(session, fund_code)
        
        if not dividends:
            return {
                'total_dividends': 0,
                'total_amount': 0.0,
                'avg_amount': 0.0,
                'dividend_frequency': 0.0
            }
        
        total_amount = sum(float(d.dividend_amount) for d in dividends)
        avg_amount = total_amount / len(dividends)
        
        # 计算分红频率（每年分红次数）
        if len(dividends) > 1:
            first_date = dividends[-1].dividend_date
            last_date = dividends[0].dividend_date
            years = (last_date - first_date).days / 365.25
            frequency = len(dividends) / years if years > 0 else 0
        else:
            frequency = 0
        
        return {
            'total_dividends': len(dividends),
            'total_amount': total_amount,
            'avg_amount': avg_amount,
            'dividend_frequency': frequency
        }


class FundPerformanceDAO(FinancialDataDAO[FundPerformance]):
    """基金业绩数据访问层"""
    
    model = FundPerformance
    
    async def get_by_fund_code(self, session: AsyncSession, fund_code: str) -> List[FundPerformance]:
        """获取基金业绩历史"""
        stmt = (
            select(self.model)
            .where(self.model.fund_code == fund_code)
            .order_by(desc(self.model.stat_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_latest_performance(self, session: AsyncSession, fund_code: str) -> Optional[FundPerformance]:
        """获取基金最新业绩"""
        stmt = (
            select(self.model)
            .where(self.model.fund_code == fund_code)
            .order_by(desc(self.model.stat_date))
            .limit(1)
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_performance_by_period(
        self,
        session: AsyncSession,
        fund_code: str,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None
    ) -> List[FundPerformance]:
        """获取指定期间的基金业绩数据"""
        conditions = [self.model.fund_code == fund_code]
        
        if start_date:
            conditions.append(self.model.stat_date >= start_date)
        if end_date:
            conditions.append(self.model.stat_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.stat_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_top_performers(
        self,
        session: AsyncSession,
        period: str = 'return_1y',
        limit: int = 50
    ) -> List[FundPerformance]:
        """获取业绩表现最好的基金"""
        # 根据期间选择排序字段
        order_field = getattr(self.model, period, self.model.return_1y)
        
        stmt = (
            select(self.model)
            .where(order_field.is_not(None))
            .order_by(desc(order_field))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_risk_analysis(
        self,
        session: AsyncSession,
        fund_code: str
    ) -> Dict[str, Any]:
        """获取基金风险分析数据"""
        latest_performance = await self.get_latest_performance(session, fund_code)
        
        if not latest_performance:
            return {
                'sharpe_ratio': None,
                'max_drawdown': None,
                'volatility': None,
                'beta': None,
                'alpha': None
            }
        
        return {
            'sharpe_ratio': float(latest_performance.sharpe_ratio) if latest_performance.sharpe_ratio else None,
            'max_drawdown': float(latest_performance.max_drawdown) if latest_performance.max_drawdown else None,
            'volatility': float(latest_performance.annual_volatility) if latest_performance.annual_volatility else None,
            'beta': float(latest_performance.beta) if latest_performance.beta else None,
            'alpha': float(latest_performance.alpha) if latest_performance.alpha else None
        }